import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Scanner;


public class Query
{	
    private Connection con;
    private  ConnectDb dCon;
    private ArrayList<String> pnums;
    private String tableName,manuf,model,year,
    				liters, engtype,cubicin,desc,
    				rlink, table;
    
    
    
    public Query(Connection c, ConnectDb d)
    {
        pnums = new ArrayList<String>();
        this.con = c;
        this.dCon = d;
        Scanner scanner = new Scanner(System.in);
        
        queryManufac();

    }

    public ArrayList<String> queryManufac()
    {
        String query = "SELECT MANUF FROM MANUF";
        ArrayList<String> temp = new ArrayList<String>();
        try
        {
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            
            while(rs.next())
            	temp.add(rs.getString(1));
                //System.out.println(rs.getString(1));
         
            //System.out.println("Enter the manufacturer: ");
            //scanner = new Scanner(System.in);
            //String manufac = "'"+scanner.nextLine()+"'";
            //manufac = manufac.toUpperCase();
            //System.out.println("you entered:" + manufac);
            //queryModel(manufac);
        }

        catch (SQLException ex) 
        {
            System.err.println(ex.getMessage());
        }
        return temp;
    } 
    
    public ArrayList<String> queryModel(String manuf)
    {
    	this.manuf= manuf;
        String query = "SELECT tname FROM manuf where manuf=" +"'" +manuf.toUpperCase()+"'";
        ArrayList<String> temp = new ArrayList<String>();
       
        try
        {
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            while(rs.next())
                tableName = rs.getString(1);
                        
            String query2 = "SELECT distinct model from " + tableName;
            
            try
            {
                Statement stmnt2 = con.createStatement();
                ResultSet rs2 = stmnt2.executeQuery(query2);
                while(rs2.next())
                	temp.add(rs2.getString(1));

                //System.out.println(temp);
                //System.out.println("Enter the model: ");
                //manuscanner = new Scanner(System.in);
                //String model = "'"+scanner.nextLine()+"'";
                //model = model.toUpperCase();
                //queryYear(model, tableName);
            }
            
            catch (SQLException ex) 
            {
            	System.err.println(ex.getMessage());
            }
        }
        catch (SQLException ex)
        {
        	System.err.println(ex.getMessage());
        }
		return temp;
    }
    
    public ArrayList<String> queryYear(String model)
    {
    	this.model = model;
    	 ArrayList<String> temp = new ArrayList<String>();
System.out.println("model-----"+ model);

        String query = "Select DISTINCT year from " + tableName + " where model =" +"'"+ model.trim()+"'";
        
        try
        {          
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            while(rs.next())
               temp.add(rs.getString(1));
            //System.out.println("Enter the year: ");
            //scanner = new Scanner(System.in);
           // String year = "'"+scanner.next()+"'";
            //year = year.toUpperCase();
            //queryLitres(tName, model, year);
        }
        
        catch (SQLException ex) 
        {
        	System.err.println(ex.getMessage());
        }
        return temp;
    }
    
    public ArrayList<String> queryLitres( String year)
    {
    	this.year = year;
        String query = "Select DISTINCT litres from " + this.tableName + "where model ='" + this.model + "' AND YEAR='"+year +"'";
        ArrayList<String> temp = new ArrayList<String>();
        try
        {          
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            while(rs.next())
            	temp.add(rs.getString(1));
                //System.out.println(rs.getString(1));
            
           /* System.out.println("Enter the litres: ");
            scanner = new Scanner(System.in);
            String litres = "'"+scanner.next()+"'";
            litres = litres.toUpperCase();
            queryEngineType(tName, model, year, litres);*/
        }
        
        catch (SQLException ex) 
        {
        System.err.println(ex.getMessage());
        }
        return temp;
    }
    
    public  ArrayList<String> queryEngineType( String liters)
    {
    	this.liters=liters;
        String query = "Select DISTINCT engine_type from " + this.tableName + "where model ='" + 
        				this.model + "' AND YEAR='"+this.year + "' AND litres='"+this.liters+ "'";
        ArrayList<String> temp = new ArrayList<String>();
        try
        {          
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            while(rs.next())
            	temp.add(rs.getString(1));
                /*System.out.println(rs.getString(1));
            
            System.out.println("Enter the engine type: ");
            scanner = new Scanner(System.in);
            String engineType = "'"+scanner.next()+"'";
            engineType = engineType.toUpperCase();
            queryCubicInches(tName, model, year, litres, engineType);*/
        }
        
        catch (SQLException ex) 
        {
        System.err.println(ex.getMessage());
        }
        return temp;
    }
    
    public  ArrayList<String> queryCubicInches(String engineType)
    {
    	this.engtype = engineType;
        String query = "Select DISTINCT cubic_inches from " + this.tableName + 
        					"where model ='" + this.model + "' AND YEAR='"+this.year + 
        					"' AND litres='"+this.liters + "' AND engine_type='"+ this.engtype + "'";
        ArrayList<String> temp = new ArrayList<String>();
        try
        {          
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            while(rs.next())
            	temp.add(rs.getString(1));
                /*System.out.println(rs.getString(1));
            
            System.out.println("Enter the cubic inches: ");
            scanner = new Scanner(System.in);
            String cubicInches = "'"+scanner.next()+"'";
            cubicInches = cubicInches.toUpperCase();
            queryDesc(tName, model, year, litres, engineType, cubicInches);*/
        }
        
        catch (SQLException ex) 
        {
        System.err.println(ex.getMessage());
        }
        return temp;
    }
    
    public ArrayList<String> queryDesc( String cubicInches)
    {
    	this.cubicin=cubicInches;
        String query = "Select DISTINCT description from " + this.tableName 
        					+ "where model ='" + this.model + "' AND YEAR='"+this.year 
        					+ "' AND litres='"+this.liters + "' AND engine_type='"+
        					this.engtype + "' AND cubic_inches='"+this.cubicin+"'";
        ArrayList<String> temp = new ArrayList<String>();
        try
        {          
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            while(rs.next())
            	temp.add(rs.getString(1));
                /*System.out.println(rs.getString(1));
            
            System.out.println("Enter the description: ");
            scanner = new Scanner(System.in);
            String description = "'"+scanner.nextLine()+"'";
            description = description.toUpperCase();
            queryRlink(tName, model, year, litres, engineType, cubicInches, description);*/
        }
        
        catch (SQLException ex) 
        {
        System.err.println(ex.getMessage());
        }
        return temp;
    }
    
    public ArrayList<String> queryRlink(String description)
    {
    	this.desc = description;
        String query = "Select rlink from " + this.tableName + 
        				" where model ='" + this.model + 
        				"' AND YEAR='"+this.year + "' AND litres='"+this.liters + 
        				"' AND engine_type='"+ this.engtype + 
        				"' AND cubic_inches='" + this.cubicin + 
        				"' AND description='"+this.desc + "'";
        ArrayList<String> temp = new ArrayList<String>();

        try
        {          
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            String rLink = "";
            while(rs.next())
            	temp.add(rs.getString(1));
               /* rLink = rs.getString(1);
            rLink = rLink.trim().toUpperCase();
            System.out.println(rLink);
            queryPNum(rLink);*/
        }
        
        catch (SQLException ex) 
        {
        System.err.println(ex.getMessage());
        }
        return temp;
    }
    
    
    public ArrayList<String> queryPNum(String rLink)
    {
    	this.rlink=rLink;
        String query = "Select ARS1,ARS2,ARS3,ARS4,MOD1,MOD2," +
        		"MOD3,MOD4,BEH1,BEH2,BEH3,BEH4,DAN1," +
        		"DAN2,DAN3,DAN4 from radCRX where rlink="+this.rlink;

        ArrayList<String> temp = new ArrayList<String>();

        String[] pnum = new String[16];
        try
        {          
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            while(rs.next())
            {
                for(int i =1; i < 17; i++)
                {
                    pnum[i-1] = "'"+rs.getString(i)+"'";
                }
            }
            
            //print out pnum to test
            for(int i = 0; i<pnum.length; i++)
            {
                //System.out.println(pnum[i]);
                
                for(int j = i+1; j < pnum.length; j++)
                {
                    if (pnum[i].equals(pnum[j]))
                    {
                        pnum[j] = "'null'";
                    }
                }
            }
            
            for(int i = 0; i <pnum.length; i++)
            {
                if(!pnum[i].equals("'null'"))
                {
                    //rdimars
                    if(i<= 3)
                    {
                        temp.addAll(queryParts(pnum[i], "rdimars"));
                    }
                    //rdimbeh
                    else if (i<=7)
                    {
                        temp.addAll(queryParts(pnum[i], "rdimbeh"));
                    }
                    //rdimdan
                    else if (i<=11)
                    {
                        temp.addAll(queryParts(pnum[i], "rdimdan"));
                    }
                    //rdimmod
                    else
                    {
                        temp.addAll(queryParts(pnum[i], "rdimmod"));
                    }
                }
            }

        }
        
        catch (SQLException ex) 
        {
        System.err.println(ex.getMessage());
        }
        //pnums = temp;
        return temp;
    }
    
    public ArrayList<String> queryParts(String pnum, String table)
    {
        this.table = table;
        String query = "SELECT * FROM " + table + " WHERE P_NUMBER="+pnum;
        String amount="";
        ArrayList<String> temp = new ArrayList<String>();

        try
        {          
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            String rLink = "";
            String row="";
            while(rs.next())
            {
                for(int i = 1; i<11; i++)
                {
                    rLink = rs.getString(i);
                    if(i==1)
                        pnums.add(rLink);
                    if(i==10){
                        amount=rs.getString(i);
                    }
                    row=row+" " + rLink;
                    System.out.print(rLink+ " ");
                }
                temp.add(row);
                row="";
                
                System.out.println("");

            }/*
            System.out.println("Select part to order, enter the pnum: ");
            scanner = new Scanner(System.in);
            String upnum = scanner.nextLine();
            orderPart(upnum,table,amount);*/
        }
        
        catch (SQLException ex) 
        {
        System.err.println(ex.getMessage());
        }
     
        return temp;
    }
    
    public void delete()
    {
        String delete = "delete from " + this.table + " where p_number='" +this.pnums.get(0)+"'";
        System.out.println(delete);
        try
        {          
            Statement stmnt = con.createStatement();
            int res = stmnt.executeUpdate(delete);
            System.out.println(res);
        }
        
        catch (SQLException ex) 
        {
        System.err.println(ex.getMessage());
        }
    }
    
    
public void orderPart(String pnum, String table, String amt){
    int amount = Integer.parseInt(amt);
    amount--;
    String upamt = Integer.toString(amount);

    String query = "update " + table + " set AMOUNT="+upamt+" WHERE P_NUMBER="+pnum;
        
        try
        {    
            
            
    System.out.println("asdaw   "+query);
            Statement stmnt = con.createStatement();
            /*int update = stmnt.executeUpdate(query);
            if(update>0){
                con.commit();
            }
          */   
        }
        catch (SQLException ex) 
        {
        	System.err.println(ex.getMessage());
        }
}
    public String getTableName()
    {
        return tableName.trim();
    }




}
